/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.model.dao;

import com.sun.corba.se.impl.util.Version;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import src.model.entidades.Nota;
import src.model.entidades.Usuario;
import src.model.interfaces.DBServices;

/**
 *
 * @author Reginaldo
 */
public class DBServicesDAO extends UnicastRemoteObject implements DBServices {
    
    public DBServicesDAO() throws RemoteException {}
    private Connection con = null;
    private Statement st = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
   
    private String url = "jdbc:mysql://localhost:3306/supernotas";
    private String user = "supernotas";
    private String password = "supernotas";
        
    private boolean connectDB() throws ClassNotFoundException{
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, user, password);
            return true;
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
            return false;
        }
    }
    
    private boolean closeAllConections(){
        try {
            if(rs != null) rs.close();
            if(st != null) st.close();
            if(pst != null) pst.close();
            if(con != null) con.close();                
            return true;
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(DBServicesDAO.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
        return false;
    }
    
    @Override
    public Usuario login(String emailUsuario, String senhaUsuario) throws RemoteException {
        try {
            connectDB();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        Usuario usuario = null;
        try {
            pst = con.prepareStatement("select * from Usuario where email = ?");
            pst.setString(1, emailUsuario);
            rs = pst.executeQuery();
            if(rs.next()){
                if(rs.getString(4).equals(senhaUsuario)){
                    usuario = new Usuario(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
                } else {
                    return null; //errou senha
                }
            } else {
                return null; //usuario não existe
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeAllConections();
            return usuario;
        }        
    }

    @Override
    public boolean criarNota(String titulo, String desc, Timestamp prazo, int usuario_id) throws RemoteException {
        try {
            connectDB();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            pst = con.prepareStatement("insert into Nota(id, titulo, descricao, data_criacao, prazo, usuario_id) values(default, ?, ?, default, ?, ?)");
            pst.setString(1, titulo);
            pst.setString(2, desc);
            pst.setTimestamp(3, prazo);
            pst.setInt(4, usuario_id);
            pst.executeUpdate();         
        } catch (SQLException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeAllConections();
            return true;
        }        
    }

    @Override
    public boolean deletarNota(int id) throws RemoteException {
        try {
            connectDB();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        int retorno = 0;
        try {
            pst = con.prepareStatement("delete from Nota where id = ?");
            pst.setInt(1, id);
            retorno = pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeAllConections();
            return (retorno == 1)? true : false;
        }        
    }

    @Override
    public List<Nota> getNotas(int idUsuario) throws RemoteException {
        try {
            connectDB();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        List<Nota> notas = new ArrayList<Nota>();
        try {
            pst = con.prepareStatement("select * from Nota where usuario_id = ?");
            pst.setInt(1, idUsuario);
            rs = pst.executeQuery();
            while(rs.next()){
                notas.add(new Nota(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getTimestamp(4), rs.getTimestamp(5), rs.getInt(6)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBServicesDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeAllConections();
            return !notas.isEmpty()? notas : null;
        }        
    }    
}
